﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace week6
{
    public partial class Form1 : Form
    {
        private static string flag="school";
        private string query = "SELECT * FROM " + flag;
        public Form1()
        {
            InitializeComponent();
        }

        

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                flag = "school";
                query = "SELECT * FROM " + flag;
                DataTable dt = Sqlcmd.Select(query);
                dataGridView1.DataSource = dt;//匹配数据源显示
                dataGridView1.Columns[2].ReadOnly = true;//id值不可更改
            }
            catch { }
        }

        //学校数据显示
        private void button_school_Click(object sender, EventArgs e)
        {
            try
            {
                flag = "school";
                query = "SELECT * FROM " + flag;
                DataTable dt = Sqlcmd.Select(query);
                dataGridView1.DataSource = dt;//匹配数据源显示
                dataGridView1.Columns[2].ReadOnly = true;
                Sqlcmd.Logsadd("select", flag);
            }
            catch { }
        }

        //班级数据显示
        private void button_class_Click(object sender, EventArgs e)
        {
            try
            {
                flag = "classes";
                query = "SELECT * FROM " + flag;
                DataTable dt = Sqlcmd.Select(query);
                dataGridView1.DataSource = dt;//匹配数据源显示
                dataGridView1.Columns[2].ReadOnly = true;
                Sqlcmd.Logsadd("select", flag);//添加操作记录
            }
            catch { MessageBox.Show("查询失败！"); }
        }
        //学生数据显示
        private void button_student_Click(object sender, EventArgs e)
        {
            try
            {
                flag = "student";
                query = "SELECT * FROM " + flag;
                DataTable dt = Sqlcmd.Select(query);
                dataGridView1.DataSource = dt;//匹配数据源显示
                dataGridView1.Columns[2].ReadOnly = true;
                Sqlcmd.Logsadd("select", flag);//添加操作记录
            }
            catch { MessageBox.Show("查询失败！"); }
        }
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)//删除按钮
            {
                try
                {
                    string id = dataGridView1.CurrentRow.Cells[2].Value.ToString();//获取删除数据的id
                    Sqlcmd.Delete(flag, id);
                    query = "SELECT * FROM " + flag;
                    DataTable dt = Sqlcmd.Select(query);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[2].ReadOnly = true;
                    Sqlcmd.Logsadd("delete", flag);//添加操作记录
                }
                catch { MessageBox.Show("删除失败！"); }
            }
            else if (e.ColumnIndex == 1)//修改按钮
            {
                try
                {
                    string id = dataGridView1.CurrentRow.Cells[2].Value.ToString();//获取id
                    string changetext="";
                    //根据不同表格获取不同的需要更改的属性值
                    if(flag=="school")//学校
                    {
                        string name = dataGridView1.CurrentRow.Cells[3].Value.ToString();
                        string adr = dataGridView1.CurrentRow.Cells[4].Value.ToString();
                        changetext = "schoolname='" + name + "',adress='" + adr+"'";
                    }
                    else if(flag=="classes")//班级
                    {
                        string school = dataGridView1.CurrentRow.Cells[3].Value.ToString();
                        string name = dataGridView1.CurrentRow.Cells[4].Value.ToString();
                        string master = dataGridView1.CurrentRow.Cells[5].Value.ToString();
                        string sum = dataGridView1.CurrentRow.Cells[6].Value.ToString();
                        changetext = "school='" + school + "',name='" + name+"',master='"+master+"',student_sum='"+sum+"'";
                    }
                    else if (flag == "student")//学生
                    {
                        string name = dataGridView1.CurrentRow.Cells[3].Value.ToString();
                        string sex = dataGridView1.CurrentRow.Cells[4].Value.ToString();
                        string classname = dataGridView1.CurrentRow.Cells[5].Value.ToString();
                        string school = dataGridView1.CurrentRow.Cells[6].Value.ToString();
                        changetext = "name='" + name + "',sex='" + sex + "',classname='" + classname + "',school='" + school + "'";
                    }
                    Sqlcmd.Change(flag, id,changetext);
                    query = "SELECT * FROM " + flag;
                    DataTable dt = Sqlcmd.Select(query);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[2].ReadOnly = true;//id的值不可更改
                    Sqlcmd.Logsadd("change", flag);//添加操作记录
                }
                catch { }
            }
        }
        //添加按钮
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                string id = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();//获取id
                string addtext = "";
                //根据不同表格获取不同的需要添加的属性值
                if (flag == "school")//学校表格
                {
                    if (dataGridView1.SelectedRows[0].Cells[2].Value.ToString() != string.Empty)
                        MessageBox.Show("请不要选择已有数据添加！");
                    string name = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                    string adr = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                    addtext = "INSERT INTO " + flag + "(schoolname,adress) VALUES('" + name + "','" + adr + "')";
                }
                else if (flag == "classes")//班级表格
                {
                    if (dataGridView1.SelectedRows[0].Cells[2].Value.ToString() != string.Empty)
                        MessageBox.Show("请不要选择已有数据添加！");
                    string school = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                    string name = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                    string master = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
                    string sum = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
                    addtext = "INSERT INTO " + flag + "(school,name,master,student_sum)VALUES('" + school + "','" + name + "','" + master + "'," + sum + ")";
                }
                else if (flag == "student")//学生表格
                {
                    if (dataGridView1.SelectedRows[0].Cells[2].Value.ToString() != string.Empty)
                        MessageBox.Show("请不要选择已有数据添加！");
                    string name = dataGridView1.CurrentRow.Cells[3].Value.ToString();
                    string sex = dataGridView1.CurrentRow.Cells[4].Value.ToString();
                    string classname = dataGridView1.CurrentRow.Cells[5].Value.ToString();
                    string school = dataGridView1.CurrentRow.Cells[6].Value.ToString();
                    addtext = "INSERT INTO " + flag + "(name,sex,classname,school)VALUES('" + name + "','" + sex + "','" + classname + "','" + school + "')";
                }
                Console.WriteLine(addtext);
                Sqlcmd.Add(flag,addtext);
                query = "SELECT * FROM " + flag;
                DataTable dt = Sqlcmd.Select(query);
                dataGridView1.DataSource = dt;
                dataGridView1.Columns[2].ReadOnly = true;
                Sqlcmd.Logsadd("add", flag);//添加操作记录
                MessageBox.Show("添加成功！");
            }
            catch { MessageBox.Show("添加失败！"); }
        }

        //查看操作记录
        private void button_log_Click(object sender, EventArgs e)
        {
            try
            {
                flag = "logs";
                DataTable dt = Sqlcmd.Select("SELECT * FROM logs");
                dataGridView1.DataSource = dt;
                dataGridView1.ReadOnly = true;
                dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].Visible = false;
                dataGridView1.Columns[3].Width = 150;
                dataGridView1.Columns[4].Width = 200;
            }
            catch { }
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            try
            {
                Sqlcmd.Dellogs();//每次退出时删除记录
            }
            catch { }
        }

        //查询按钮
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (flag == "logs")//操作记录界面时
                {
                    MessageBox.Show("该页面不支持查找！");
                    return;
                }
                else if (box_class.Text==""&&box_name.Text==""&&box_school.Text=="")
                {
                    query = "SELECT * FROM " + flag;
                    DataTable d = Sqlcmd.Select(query);
                    dataGridView1.DataSource = d;//匹配数据源显示
                    dataGridView1.Columns[2].ReadOnly = true;
                    return;
                }
                
                else if (flag == "school")//学校界面时
                {
                    string schoolinf = box_school.Text;
                    query = "SELECT * FROM school WHERE schoolname LIKE '" + schoolinf+"%%'";
                    DataTable dt = Sqlcmd.Select(query);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[2].ReadOnly = true;
                    Sqlcmd.Logsadd("search", flag);
                }
                else if(flag=="classes")//班级界面时
                {
                    string schoolinf = box_school.Text;
                    string classinf = box_class.Text;
                    query = "SELECT * FROM classes WHERE school LIKE '" + schoolinf + "%%' AND name LIKE '"+classinf+"%%'";
                    DataTable dt = Sqlcmd.Select(query);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[2].ReadOnly = true;
                    Sqlcmd.Logsadd("search", flag);
                }
                else if(flag=="student")//学生界面时
                {
                    string schoolinf = box_school.Text;
                    string classinf = box_class.Text;
                    string stuinf = box_name.Text;
                    query = "SELECT * FROM student WHERE school LIKE '" + schoolinf + "%%' AND name LIKE '" + stuinf + "%%' AND classname LIKE '"+classinf+"%%'";
                    DataTable dt = Sqlcmd.Select(query);
                    dataGridView1.DataSource = dt;
                    dataGridView1.Columns[2].ReadOnly = true;
                    Sqlcmd.Logsadd("search", flag);
                }

            }
            catch { }
        }

    }
}
